/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package persistence;

import com.sun.rowset.CachedRowSetImpl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author Martin Giry
 */
public class Bdd {
    private String login = "root";
    private String motdepasse = "";
    private String serveur = "localhost";
    private String bdd = "musicmap";
    private String pilote = "com.mysql.jdbc.Driver";
    private Connection connexion;


    public Bdd(){
     
        try {
            Class.forName(pilote);
            connexion = DriverManager.getConnection("jdbc:mysql://"+serveur+"/"+bdd+"?useUnicode=true&characterEncoding=UTF-8",login,motdepasse);
        }
        catch (ClassNotFoundException ex) {
            Logger.getLogger(Bdd.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch(SQLException ex){
             Logger.getLogger(Bdd.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ResultSet select(String sql){
        return select(sql, null);
    }

    public ResultSet select(String sql, Object[] argsList){
        CachedRowSetImpl set = null;
        try {
            set = new CachedRowSetImpl();
            set.setCommand(sql);
            if (argsList != null) for(int i=0; i<argsList.length; i++) set.setObject(i+1, argsList[i]);
            set.execute(connexion);
        } catch (SQLException ex) {
            Logger.getLogger(Bdd.class.getName()).log(Level.SEVERE, null, ex);
        }
        return set;
    }

    public void update(String sql){
        update(sql, null);
    }

    public void update(String sql, Object[] argsList){
        PreparedStatement stWrite;
        try{
            stWrite = connexion.prepareStatement(sql);
            try{
                if (argsList != null) for(int i=0; i<argsList.length; i++) stWrite.setObject(i+1, argsList[i]);
                stWrite.executeUpdate();
            } finally {
                stWrite.close();
            }
        }catch (SQLException ex) {
            Logger.getLogger(Bdd.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void mass_insert(String sql, List<? extends Object []> list){
        if(list.isEmpty()) return;
        PreparedStatement stWrite;
        StringBuilder sb = new StringBuilder();
        sb.append(sql);
        sb.append(" values");
        for(int i=0; i<list.size(); i++){
            sb.append("(");
            for(int j=0; j<list.get(0).length; j++) sb.append("?,");
            sb.deleteCharAt(sb.length()-1);
            sb.append("),");
        }
        sb.deleteCharAt(sb.length()-1);
        sb.append(";");
        try{
            stWrite = connexion.prepareStatement(sb.toString());
            try{
                int i=1;
                for(Object[] ol:list) for(Object o:ol)stWrite.setObject(i++, o);
                stWrite.executeUpdate();
            } finally {
                stWrite.close();
            }
        }catch (SQLException ex) {
            Logger.getLogger(Bdd.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void close(){
        try {
            connexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(Bdd.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
